﻿using System;
using System.Data;
using System.Data.Common;
using System.Text;
using Besture.Common;
using Besture.Model;

namespace Besture.Dal
{
    public partial class RoomDal : BaseDataAccess
    {
 
        public DataTable GetRoomByIDorHotelsID(int iID, int iHotelsID)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format("SELECT * FROM TBRoom where HotelsID={0} or ID= {1} ORDER BY ID DESC ",iHotelsID,iID));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }


        /// <summary>
        /// 获取详细信息
        /// </summary>
        /// <param name="id">主键值</param>
        /// <returns></returns>
        public RoomModel GetModel(int id)
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append("SELECT * FROM TBRoom");
                selectSql.Append(" WHERE ");
                selectSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                if (dt.Rows.Count > 0)
                    return ModelConvertHelper<RoomModel>.ConvertToModelList(dt)[0];
                else return null;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 获取全部数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable()
        {
            try
            {
                StringBuilder selectSql = new StringBuilder();
                selectSql.Append(string.Format(@"
                SELECT tr.*,th.HotelsName,trc.CategoryName AS BedTypeName,trc1.CategoryName AS BroadbandName,trc2.CategoryName AS  SmokeName FROM dbo.TBRoom tr
                INNER JOIN dbo.TBHotels th ON th.ID=tr.HotelsID AND th.IsDeleted=0 AND tr.IsDeleted=0
                INNER JOIN dbo.TBRoomCategory trc ON trc.ID=tr.BedType AND trc.IsDeleted=0 AND tr.IsDeleted=0 AND trc.CategoryType=1
                INNER JOIN dbo.TBRoomCategory trc1 ON trc1.ID = tr.Broadband AND trc1.IsDeleted=0 AND tr.IsDeleted=0 AND trc1.CategoryType=3
                INNER JOIN dbo.TBRoomCategory trc2 ON trc2.ID=tr.Smoke AND trc2.IsDeleted=0 AND tr.IsDeleted=0 AND trc2.CategoryType=4
                ORDER BY tr.CreateTime DESC "));
                DbCommand ldcDBCommand = base.GetCommand(selectSql.ToString());
                DataTable dt = base.GetDataSet(ldcDBCommand).Tables[0];
                return dt;
            }
            catch (Exception exception)
            {
                throw exception;
            }
        }

        /// <summary>
        /// 添加
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Add(RoomModel model)
        {
            var identityId = -1;
            try
            {

				
            string sqlString = string.Format(@"
                INSERT INTO TBRoom
                (
                        HotelsID
                        ,RoomName
                        ,RoomImage
                        ,BedType
                        ,BedTypeRemark
                        ,Breakfast
                        ,Broadband
                        ,RoomArea
                        ,Floor
                        ,LowPrice
                        ,PeopleNumber
                        ,RoomRemark
                        ,BookStatus
                        ,Smoke
                        ,Price
                        ,OneDealerPrice
                        ,TwoDealerPrice
                        ,ThreeDealerPrice
                        ,SortID
                        ,IsDeleted
                        ,Created
                        ,CreateTime
                        ,Updated
                        ,UpdateTime
                        ,RoomFacilities
                )
                VALUES
                (
                        @HotelsID
                       ,@RoomName
                       ,@RoomImage
                       ,@BedType
                       ,@BedTypeRemark
                       ,@Breakfast
                       ,@Broadband
                       ,@RoomArea
                       ,@Floor
                       ,@LowPrice
                       ,@PeopleNumber
                       ,@RoomRemark
                       ,@BookStatus
                       ,@Smoke
                       ,@Price
                       ,@OneDealerPrice
                       ,@TwoDealerPrice
                       ,@ThreeDealerPrice
                       ,@SortID
                       ,@IsDeleted
                       ,@Created
                       ,@CreateTime
                       ,@Updated
                       ,@UpdateTime
                       ,@RoomFacilities
                )SELECT @@IDENTITY");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);

            //参数设定
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@RoomName", DbType.String, model.RoomName);
            SetParameter(ldcDBCommand, "@RoomImage", DbType.String, model.RoomImage);
            SetParameter(ldcDBCommand, "@BedType", DbType.Int32, model.BedType);
            SetParameter(ldcDBCommand, "@BedTypeRemark", DbType.String, model.BedTypeRemark);
            SetParameter(ldcDBCommand, "@Breakfast", DbType.Int32, model.Breakfast);
            SetParameter(ldcDBCommand, "@Broadband", DbType.Int32, model.Broadband);
            SetParameter(ldcDBCommand, "@RoomArea", DbType.Int32, model.RoomArea);
            SetParameter(ldcDBCommand, "@Floor", DbType.String, model.Floor);
            SetParameter(ldcDBCommand, "@LowPrice", DbType.Int32, model.LowPrice);
            SetParameter(ldcDBCommand, "@PeopleNumber", DbType.Int32, model.PeopleNumber);
            SetParameter(ldcDBCommand, "@RoomRemark", DbType.String, model.RoomRemark);
            SetParameter(ldcDBCommand, "@BookStatus", DbType.Int32, model.BookStatus);
            SetParameter(ldcDBCommand, "@Smoke", DbType.Int32, model.Smoke);
            SetParameter(ldcDBCommand, "@Price", DbType.Decimal, model.Price);
            SetParameter(ldcDBCommand, "@OneDealerPrice", DbType.Decimal, model.OneDealerPrice);
            SetParameter(ldcDBCommand, "@TwoDealerPrice", DbType.Decimal, model.TwoDealerPrice);
            SetParameter(ldcDBCommand, "@ThreeDealerPrice", DbType.Decimal, model.ThreeDealerPrice);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@CreateTime", DbType.DateTime, model.CreateTime);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
            SetParameter(ldcDBCommand, "@RoomFacilities", DbType.String, model.RoomFacilities);

                identityId = base.ExecuteScalarInsert(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return identityId;
        }

        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int Update(RoomModel model)
        {
            int iTmp = -1;
            try
            {
				
            string sqlString = string.Format(@"
                UPDATE TBRoom
                SET
                    HotelsID = @HotelsID
                    ,RoomName = @RoomName
                    ,RoomImage = @RoomImage
                    ,BedType = @BedType
                    ,BedTypeRemark = @BedTypeRemark
                    ,Breakfast = @Breakfast
                    ,Broadband = @Broadband
                    ,RoomArea = @RoomArea
                    ,Floor = @Floor
                    ,LowPrice = @LowPrice
                    ,PeopleNumber = @PeopleNumber
                    ,RoomRemark = @RoomRemark
                    ,BookStatus = @BookStatus
                    ,Smoke = @Smoke
                    ,Price = @Price
                    ,OneDealerPrice = @OneDealerPrice
                    ,TwoDealerPrice = @TwoDealerPrice
                    ,ThreeDealerPrice = @ThreeDealerPrice
                    ,SortID = @SortID
                    ,IsDeleted = @IsDeleted
                    ,Created = @Created
                    ,Updated = @Updated
                    ,UpdateTime = @UpdateTime
                    ,RoomFacilities = @RoomFacilities
                WHERE
                    ID = @ID
            ");

            DbCommand ldcDBCommand = base.GetCommand(sqlString);
            //绑定SQL参数
            SetParameter(ldcDBCommand, "@ID", DbType.Int32, model.ID);
            SetParameter(ldcDBCommand, "@HotelsID", DbType.Int32, model.HotelsID);
            SetParameter(ldcDBCommand, "@RoomName", DbType.String, model.RoomName);
            SetParameter(ldcDBCommand, "@RoomImage", DbType.String, model.RoomImage);
            SetParameter(ldcDBCommand, "@BedType", DbType.Int32, model.BedType);
            SetParameter(ldcDBCommand, "@BedTypeRemark", DbType.String, model.BedTypeRemark);
            SetParameter(ldcDBCommand, "@Breakfast", DbType.Int32, model.Breakfast);
            SetParameter(ldcDBCommand, "@Broadband", DbType.Int32, model.Broadband);
            SetParameter(ldcDBCommand, "@RoomArea", DbType.Int32, model.RoomArea);
            SetParameter(ldcDBCommand, "@Floor", DbType.String, model.Floor);
            SetParameter(ldcDBCommand, "@LowPrice", DbType.Int32, model.LowPrice);
            SetParameter(ldcDBCommand, "@PeopleNumber", DbType.Int32, model.PeopleNumber);
            SetParameter(ldcDBCommand, "@RoomRemark", DbType.String, model.RoomRemark);
            SetParameter(ldcDBCommand, "@BookStatus", DbType.Int32, model.BookStatus);
            SetParameter(ldcDBCommand, "@Smoke", DbType.Int32, model.Smoke);
            SetParameter(ldcDBCommand, "@Price", DbType.String, model.Price);
            SetParameter(ldcDBCommand, "@OneDealerPrice", DbType.String, model.OneDealerPrice);
            SetParameter(ldcDBCommand, "@TwoDealerPrice", DbType.String, model.TwoDealerPrice);
            SetParameter(ldcDBCommand, "@ThreeDealerPrice", DbType.String, model.ThreeDealerPrice);
            SetParameter(ldcDBCommand, "@SortID", DbType.Int32, model.SortID);
            SetParameter(ldcDBCommand, "@IsDeleted", DbType.Int32, model.IsDeleted);
            SetParameter(ldcDBCommand, "@Created", DbType.Int32, model.Created);
            SetParameter(ldcDBCommand, "@Updated", DbType.Int32, model.Updated);
            SetParameter(ldcDBCommand, "@UpdateTime", DbType.DateTime, model.UpdateTime);
            SetParameter(ldcDBCommand, "@RoomFacilities", DbType.String, model.RoomFacilities);


                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 单个删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("DELETE FROM TBRoom");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID=@ID ");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.Int32, id);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }

        /// <summary>
        /// 批量删除
        /// </summary>
        /// <param name="idList"></param>
        /// <returns></returns>
        public int DeleteList(string ids)
        {
            int iTmp = -1;
            try
            {
                StringBuilder updateSql = new StringBuilder();
                updateSql.Append("exec('DELETE FROM TBRoom");
                updateSql.Append(" WHERE ");
                updateSql.Append(" ID in ('+@ID+') ')");
                DbCommand ldcDBCommand = base.GetCommand(updateSql.ToString());
                base.SetParameter(ldcDBCommand, "@ID", DbType.String, ids);
                iTmp = base.ExecuteNonQuery(ldcDBCommand);
            }
            catch (Exception exception)
            {
                throw exception;
            }
            return iTmp;
        }
    }
}

